﻿using System;
using System.Text;
using System.Data;
using CRMFramework;

namespace CRMBiz
{
    public static class DuplicateContactHandler
    {
        public static DataSet SelectDuplicateContact(int pageIndex, int pageSize, string searchfield)
        {

            DataSet ds;
            try
            {
                var sbSql = new StringBuilder("Select table1.* from (Select ");
                string[] columns = { };
                var strquery = new StringBuilder();

                String strWhr = " Where ISNULL(DupwithContact) AND  deletedFlag=0 ";
                String strName = " '<span style=\"color:green\">',trim(CONCAT(UCASE(SUBSTRING(FirstName, 1, 1)),LOWER(SUBSTRING(FirstName, 2)))),' ',trim(CONCAT(UCASE(SUBSTRING(MiddleName, 1, 1)),LOWER(SUBSTRING(MiddleName, 2)))),' ',trim(CONCAT(UCASE(SUBSTRING(SurName, 1, 1)),LOWER(SUBSTRING(SurName, 2)))) ,'</span>' ";
                String strLeftJoin = " LEFT JOIN  (Select DISTINCT DupwithContact  as tempid from crm_contact where DupwithContact is not null  AND  DupwithContact<>0 AND  DupwithContact <> '') tblDuplicat1  ON  table1.ContactID=tblDuplicat1.tempid where ISNULL(tblDuplicat1.tempid) ";

                if (!string.IsNullOrEmpty(searchfield))
                {
                    if (searchfield.Contains("/"))  // Multiple
                    {
                        columns = searchfield.Split('/');
                        if (columns[0] == "FullName")
                            if (columns[1] != "app_ipaddress")
                            {

                                strquery.Append(" INNER JOIN (Select FirstName as fname, MiddleName as mname,SurName as sname ,").Append(columns[1]).Append(" as dob ,count(*) as Total FROM crm_contact ").Append(strWhr)
                                    .Append("  AND FirstName IS NOT Null AND trim(FirstName) <> ''  AND  MiddleName IS NOT Null AND trim(MiddleName) <> '' AND  SurName IS NOT Null AND trim(SurName) <> ''   AND  ").Append(columns[1]).Append(" IS NOT Null AND trim(").Append(columns[1]).Append(") <> '' ")
                                    .Append(" GROUP BY FirstName,MiddleName,SurName,").Append(columns[1]).Append(" having count(*) > 1 ) tblDup ")
                                    .Append(" Where trim(tblDup.fname)=trim(FirstName) and trim(tblDup.mname)=trim(MiddleName) and trim(tblDup.sname)=trim(SurName)  and trim(tblDup.dob)=trim(").Append(columns[1]).Append(")");


                                sbSql.Append(" CAST(CONCAT(").Append(strName).Append(",'   ','<span style=\"color:orange;padding-left:10px\">', trim(").Append(columns[1]).Append("),'</span>') AS Char) as FullName,")
                                     .Append(" CAST(CONCAT(trim(FirstName),' ',trim(MiddleName),' ',trim(SurName) ,' ', trim(").Append(columns[1]).Append(")) AS Char) as DetectiveBy,")
                                     .Append(" ContactID, CreateDate,app_id,tblDup.total as DuplicateCount, ")
                                     .Append(" (Select count(*)  from (Select ContactID FROM crm_contact ").Append(strquery.ToString()).Append(" )table1 ").Append(strLeftJoin)
                                     .Append(" ) AS Total FROM crm_contact ")
                                     .Append(strquery.ToString());
                            }
                            else
                            {
                                strquery.Append(" INNER join crm_loaninfo on crm_contact.app_id=crm_loaninfo.app_id ")
                                            .Append(" INNER JOIN (Select  FirstName as fname, MiddleName as mname,SurName as sname ,crm_loaninfo.").Append(columns[1]).Append(" as dob ,count(*) as Total FROM crm_contact ")
                                            .Append(" INNER join crm_loaninfo on crm_contact.app_id=crm_loaninfo.app_id ").Append(strWhr)
                                             .Append(" AND  FirstName IS NOT Null AND trim(FirstName) <> '' AND  MiddleName IS NOT Null AND trim(MiddleName) <> '' AND  SurName IS NOT Null AND trim(SurName) <> '' AND ")
                                              .Append(columns[1]).Append(" IS NOT Null AND trim(").Append(columns[1]).Append(") <> '' ")
                                            .Append(" GROUP BY FirstName,MiddleName,SurName,crm_loaninfo.").Append(columns[1]).Append(" having count(*) > 1 ) tblDup ")
                                            .Append(" Where trim(tblDup.fname)=trim(FirstName) and trim(tblDup.mname)=trim(MiddleName) and trim(tblDup.sname)=trim(SurName)  and trim(tblDup.dob)=trim(crm_loaninfo.").Append(columns[1]).Append(")");

                                sbSql.Append("CAST(CONCAT(").Append(strName).Append(" ,' ','<span style=\"color:orange;padding-left:10px\">', trim(crm_loaninfo.").Append(columns[1]).Append("),'</span>') AS Char) as FullName,")
                                .Append(" CAST(CONCAT(trim(FirstName),' ',trim(MiddleName),' ',trim(SurName) ,'    ', trim(crm_loaninfo.").Append(columns[1]).Append(")) AS Char) as DetectiveBy,")
                                .Append(" ContactID, CreateDate,crm_contact.app_id,tblDup.total as DuplicateCount,(Select count(*)  from (Select ContactID FROM crm_contact ").Append(strquery.ToString()).Append(" )table1 ")
                                .Append(strLeftJoin).Append(" ) AS Total FROM crm_contact ").Append(strquery.ToString());
                            }
                    }
                    else  //Single
                    {
                        if (searchfield == "FullName")
                        {
                            strquery.Append(" INNER JOIN (Select FirstName as fname, MiddleName as mname,SurName as sname ,count(*) as Total FROM crm_contact ").Append(strWhr)
                                 .Append(" AND FirstName IS NOT Null AND trim(FirstName) <> '' AND MiddleName IS NOT Null AND trim(MiddleName) <> '' AND SurName IS NOT Null AND trim(SurName) <> '' ")
                                        .Append(" GROUP BY FirstName,MiddleName,SurName having count(*) > 1 ) tblDup ")
                                        .Append(" Where trim(tblDup.fname)=trim(FirstName) and trim(tblDup.mname)=trim(MiddleName) and trim(tblDup.sname)=trim(SurName) ");

                            sbSql.Append(" CAST(CONCAT(").Append(strName).Append(") as Char) as FullName, CAST(CONCAT(trim(FirstName),' ',trim(MiddleName),' ',trim(SurName)) AS CHAR) as DetectiveBy,")
                                 .Append(" ContactID, CreateDate,app_id,tblDup.total as DuplicateCount, ")
                                 .Append(" (Select count(*)  from (Select ContactID FROM crm_contact ").Append(strquery.ToString()).Append(" )table1 ").Append(strLeftJoin)
                                 .Append(" ) AS Total FROM crm_contact ")
                                 .Append(strquery.ToString());
                        }
                        else if (searchfield == "Address")
                        {
                            strquery.Append(" INNER JOIN (Select Street as fname, Suburb as mname,State as sname , PostCode as pin,count(*) as Total FROM crm_contact ").Append(strWhr)
                                        .Append(" AND Street IS NOT Null AND trim(Street) <> ''  AND Suburb IS NOT Null AND trim(Suburb) <> '' AND State IS NOT Null AND trim(State) <> '' AND PostCode IS NOT Null AND trim(PostCode) <> '' ")
                                        .Append(" GROUP BY Street,Suburb,State,PostCode having count(*) > 1 ) tblDup ")
                                        .Append(" Where trim(tblDup.fname)=trim(Street) and trim(tblDup.mname)=trim(Suburb) and trim(tblDup.sname)=trim(State) and trim(tblDup.pin)=trim(PostCode) ");

                            sbSql.Append("CAST(CONCAT('<span style=\"color:green\">',trim(CONCAT(UCASE(SUBSTRING(Street, 1, 1)),LOWER(SUBSTRING(Street, 2)))),' ',trim(CONCAT(UCASE(SUBSTRING(Suburb, 1, 1)),LOWER(SUBSTRING(Suburb, 2)))),' ',trim(CONCAT(UCASE(SUBSTRING(State, 1, 1)),LOWER(SUBSTRING(State, 2)))),' ',trim(CONCAT(UCASE(SUBSTRING(PostCode, 1, 1)),LOWER(SUBSTRING(PostCode, 2)))),'</span>') AS Char) as FullName,")
                                .Append(" CAST(CONCAT(trim(Street),' ',trim(Suburb),' ',trim(State),' ',trim(PostCode)) AS CHAR) as DetectiveBy,")
                                .Append(" ContactID, CreateDate,app_id,tblDup.total as DuplicateCount, ")
                                .Append(" (Select count(*) from ( select ContactID from crm_contact ").Append(strquery.ToString()).Append(" )table1 ").Append(strLeftJoin)
                                .Append(" ) as Total FROM crm_contact ").Append(strquery.ToString());
                        }
                        else
                        {
                            strquery.Append(" INNER JOIN (select  ").Append(searchfield).Append(" as DetectiveBy ,count(*) as Total FROM crm_contact ").Append(strWhr)
                                         .Append("  AND  ").Append(searchfield).Append(" IS NOT Null AND trim(").Append(searchfield).Append(") <> '' GROUP BY ").Append(searchfield).Append("  having total > 1 ) temp ON trim( ").Append(searchfield).Append(")=trim(temp.DetectiveBy) ");

                            sbSql.Append(" CAST(CONCAT('<span style=\"color:green\">',trim(CONCAT(LOWER(SUBSTRING(").Append(searchfield).Append(", 1, 1)),LOWER(SUBSTRING(").Append(searchfield).Append(", 2)))),'</span>') AS CHAR) as FullName, trim(").Append(searchfield)
                                .Append(") as DetectiveBy, ContactID, CreateDate,app_id,temp.total as DuplicateCount, ")
                                .Append(" (Select count(*) from ( select ContactID from crm_contact ").Append(strquery).Append(" )table1 ").Append(strLeftJoin)
                                .Append(") as Total FROM crm_contact ").Append(strquery.ToString());
                        }
                    }
                }
                else
                {
                    // Defalut Search
                    strquery.Append(" INNER JOIN (Select FirstName as fname, MiddleName as mname,SurName as sname ,DateBirth as dob ,count(*) as Total FROM crm_contact ").Append(strWhr)
                                 .Append("  AND  FirstName IS NOT Null AND trim(FirstName) <> '' AND  MiddleName IS NOT Null AND trim(MiddleName) <> ''  AND  SurName IS NOT Null AND trim(SurName) <> '' AND  DateBirth IS NOT Null AND trim(DateBirth) <> '' ")
                               .Append(" GROUP BY FirstName,MiddleName,SurName,DateBirth having count(*) > 1 ) tblDup ")
                               .Append(" Where trim(tblDup.fname)=trim(FirstName) and trim(tblDup.mname)=trim(MiddleName) and trim(tblDup.sname)=trim(SurName)  and trim(tblDup.dob)=trim(DateBirth)");

                    sbSql.Append(" CAST(CONCAT(").Append(strName).Append(",' ','<span style=\"color:orange;padding-left:10px\">', trim(DateBirth),'</span>') as Char) as FullName, ")
                        .Append(" CAST(CONCAT(trim(FirstName),' ',trim(MiddleName),' ',trim(SurName) ,' ', trim(DateBirth)) AS Char) as DetectiveBy,")
                        .Append(" ContactID, CreateDate,app_id,tblDup.total as DuplicateCount ,")
                        .Append(" (Select count(*) from ( select ContactID from crm_contact ").Append(strquery).Append(" )table1 ").Append(strLeftJoin)
                        .Append(" ) as Total FROM crm_contact ")
                        .Append(strquery);
                }

                sbSql.Append(" )table1 ").Append(strLeftJoin)
                    .Append(" ORDER BY  DuplicateCount Desc,DetectiveBy ");
                sbSql.Append(" LIMIT ").Append((pageIndex - 1) * pageSize).Append(", ").Append(pageSize);
                ds = DBhelper.ExecuteDataset(sbSql.ToString());

            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }

        public static DataSet GetDuplicatClients(string field, string value)
        {
            DataSet ds;
            try
            {
                var strQuery = new StringBuilder("Select ContactID from crm_contact ");
                string[] columns = { };
                if (field.Contains("/"))  // Multiple
                {
                    columns = field.Split('/');
                    if (columns[1] != "app_ipaddress")
                        strQuery.Append(" Where CAST(Concat(trim(FirstName),' ',trim(MiddleName),' ',trim(SurName),' ' ,trim(").Append(columns[1]).Append(")) as Char)='").Append(value).Append("'");
                    else
                        strQuery.Append(" INNER JOIN crm_loaninfo on crm_loaninfo.app_id=crm_contact.app_id  Where  CAST(Concat(trim(FirstName),' ',trim(MiddleName),' ',trim(SurName),' ',trim(crm_loaninfo.app_ipaddress)) as Char)='").Append(value).Append("'");
                }
                else  //Single
                {
                    if (field == "FullName")
                        strQuery.Append(" Where CAST(Concat(trim(FirstName),' ',trim(MiddleName),' ',trim(SurName)) as Char)='").Append(value).Append("'");
                    else if (field == "Address")
                        strQuery.Append(" Where CAST(CONCAT(trim(Street),' ',trim(Suburb),' ',trim(State),' ',trim(PostCode)) AS CHAR)='").Append(value).Append("'");
                    else
                        strQuery.Append(" Where ").Append(field).Append("='").Append(value).Append("'");
                }
                strQuery.Append(" AND deletedFlag=0 AND ISNULL(DupwithContact)");
                ds = DBhelper.ExecuteDataset(strQuery.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }


        // This Method is called from ContactProfile for Duplicate Single Client 
        public static bool RemoveDuplicateClient(int ContactID, int DupWithContact, string historyOperator)
        {
            bool flag = false;
            try
            {
                var para = new[]{
                    DBhelper.SetParameter("@p_ContactID",ContactID),
                    DBhelper.SetParameter("@p_DupwithContact",DupWithContact),
                    DBhelper.SetParameter("@p_Action","Remove Duplicate"),
                    DBhelper.SetParameter("@p_historyOperator",historyOperator)
                };

                DBhelper.ExecuteNonQuerySP("sp_RemoveDuplicateClient", para);
                flag = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        // This method is called from CompareDuplicate Page  for Duplicate Multiple Clients

        public static bool duplicateMultipleClient(string allClient, int duplicateWith, string historyOperator)
        {
            bool flag = false;
            try
            {
                string[] ClientList = allClient.Split(',');
                for (int i = 0; i < ClientList.Length; i++)
                {
                    var para = new[]{
                        DBhelper.SetParameter("@p_ContactID", ClientList[i]),
                        DBhelper.SetParameter("@p_DupwithContact",duplicateWith),
                        DBhelper.SetParameter("@p_Action","Remove Duplicate (Detective)"),
                        DBhelper.SetParameter("@p_historyOperator",historyOperator)
                    };

                    DBhelper.ExecuteNonQuerySP("sp_RemoveDuplicateClient", para);
                }
                flag = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        private static DataTable GenerateTransposedTable(DataTable inputTable)
        {
            DataTable outputTable = new DataTable();

            // Add columns by looping rows

            // Header row's first column is same as in inputTable
            outputTable.Columns.Add(inputTable.Columns[0].ColumnName.ToString());

            // Header row's second column onwards, 'inputTable's first column taken
            foreach (DataRow inRow in inputTable.Rows)
            {
                string newColName = inRow[0].ToString();
                outputTable.Columns.Add(newColName);
            }

            // Add rows by looping columns        
            for (int rCount = 1; rCount <= inputTable.Columns.Count - 1; rCount++)
            {
                DataRow newRow = outputTable.NewRow();

                // First column is inputTable's Header row's second column
                newRow[0] = inputTable.Columns[rCount].ColumnName.ToString();
                for (int cCount = 0; cCount <= inputTable.Rows.Count - 1; cCount++)
                {
                    string colValue = inputTable.Rows[cCount][rCount].ToString();
                    newRow[cCount + 1] = colValue;
                }
                outputTable.Rows.Add(newRow);
            }

            return outputTable;
        }
        public static DataTable CompareDuplicateContact(int ContactID, string FirstName, string SurName, string DateBirth)
        {
            DataRow dr;
            DataTable dt = null;
            try
            {
                dt = new DataTable();

                //string strWhere = " OR ( Email='" + EMail + "' AND Email IS NOT NULL AND Email <>'' ) OR ( secondEmailAddress='" + secondEmailAddress + "' AND secondEmailAddress IS NOT NULL AND secondEmailAddress <>'' )  OR ( Mobile='" + Mobile + "' AND Mobile IS NOT NULL AND Mobile <>'' ) OR (Street='" + Street + "' AND State='" + State + "' AND Suburb='" + Suburb + "'))";

                var sQuery = new StringBuilder("Select ContactID,CONCAT(FirstName, ' ',MiddleName,' ',SurName) as FullName,CAST(Date_Format(CreateDate,'%d-%b-%Y %T') AS Char) as CreateDate, IFNULL(CAST(DATE_FORMAT(DateBirth,'%d-%b-%Y') as Char),'') as DateBirth,")
                            .Append(" Mobile,HomePhone,Email,DriverLicense,Citizenship,Street,Suburb,State,PostCode ,app_id as CreateByAppForm , CAST(Date_Format(CreateDate,'%d-%b-%Y ') AS Char) as FormDate FROM crm_contact ")
                            //.Append(" Left Join app_info on app_info.app_id=crm_contact.app_id ")
                            .Append(" where deletedFlag=0 AND (DupwithContact IS NULL OR DupwithContact=0) AND (")
                            .Append(string.Format(" FirstName like '{0}%' AND SurName= '{1}' AND  DateBirth='{2}'", FirstName, SurName, DateBirth))
                            .Append(") ORDER By ContactID Desc");

                DataSet ds = DBhelper.ExecuteDataset(sQuery.ToString());

                dt = GenerateTransposedTable(ds.Tables[0]);

                dr = dt.NewRow();
                dt.Rows.Add(dr);

                dr = dt.NewRow();
                dr["ContactID"] = "Application Profile";
                dt.Rows.Add(dr);

                dr = dt.NewRow();
                dr["ContactID"] = "Loan Amount";
                dt.Rows.Add(dr);

                dr = dt.NewRow();
                dr["ContactID"] = "Submit Date";
                dt.Rows.Add(dr);

                dr = dt.NewRow();
                dr["ContactID"] = "Security";
                dt.Rows.Add(dr);

                dr = dt.NewRow();
                dt.Rows.Add(dr);

                dr = dt.NewRow();
                dr["ContactID"] = "Give each person No";
                dt.Rows.Add(dr);

                dr = dt.NewRow();
                dr["ContactID"] = "Client-Profile Action";
                dt.Rows.Add(dr);

                dr = dt.NewRow();
                dr["ContactID"] = "Application Profile (Delete)";
                dt.Rows.Add(dr);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }
    }
}